package zunge.lbe.datacollect;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;

import zunge.tools.DbConn;

public class StatUAP {
	
	public static void stat(DbConn connIn, DbConn connOut) throws Exception{
		//Create Table uap, stat_uap
		String query = "create table uap (u_id bigint, a_id bigint, p_id bigint, d_count int, d_list text, "
				+ "primary key (u_id,a_id,p_id))";
		connIn.executeUpdate(connIn.getPreparedStatement(query));
		query = "create table stat_uap (id bigint auto_increment primary key, u_id bigint, a_id bigint, p_id int, "
				+ "d_final int, d_previous int, d_backandforth int, index(u_id), index(a_id) )";
		connIn.executeUpdate(connIn.getPreparedStatement(query));
		
		
		//Generate uap
		query = "insert into uap (u_id, a_id, p_id, d_count, d_list) "
				+ "select r.u_id, r.a_id, r.p_id, 1, concat(r.time, ',', r.decision) from record_uap r "
					+ "on duplicate key update d_count = d_count+1, d_list = concat(d_list,';',values(d_list))";
		connIn.executeUpdate(connIn.getPreparedStatement(query));
		
		//Generate stat_uap
		query = "select u_id, a_id, p_id, d_count, d_list from uap";
		PreparedStatement ps_stat_uap = connOut.getPreparedStatement(
				"insert into stat_uap (u_id, a_id, p_id, d_final, d_previous, d_backandforth) values (?,?,?,?,?,?)");
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		ResultSet rs = connIn.executeQueryInStream(query);
		while(rs.next()){
			long u_id = rs.getLong(1);
			long a_id = rs.getLong(2);
			int p_id = rs.getInt(3);
			int d_count = rs.getInt(4);
			String d_list = rs.getString(5);
			
			HashMap<Long, Integer> decisions = new HashMap<Long,Integer>();
			String[] frags = d_list.split(";");
			for(String frag:frags){
				String[] fs = frag.split(",");
				long timestamp = sdf.parse(fs[0]).getTime();
				int decision = Integer.parseInt(fs[1]);
				decisions.put(timestamp, decision);
			}
			List<Long> timestamps = new ArrayList<Long>(decisions.keySet());
			Collections.sort(timestamps);
			
			int d_final = decisions.get(timestamps.get(timestamps.size()-1));
			int d_previous = -1;
			int d_backandforth = -1;
			if(timestamps.size()>1){
				d_previous =  decisions.get(timestamps.get(timestamps.size()-2));
				d_backandforth = 0;
				if(timestamps.size()>2){
					for(int i = 0;i < timestamps.size()-2;++i){
						if(decisions.get(timestamps.get(i))==d_previous){
							for(int j = timestamps.size()-2; j > i; --j){
								if(decisions.get(timestamps.get(j))!=d_previous){
									d_backandforth = 1;
									break;
								}
							}
							break;
						}
					}
				}
			}
			
			ps_stat_uap.setLong(1, u_id);
			ps_stat_uap.setLong(2, a_id);
			ps_stat_uap.setInt(3, p_id);
			ps_stat_uap.setInt(4, d_final);
			ps_stat_uap.setInt(5, d_previous);
			ps_stat_uap.setInt(6, d_backandforth);
			connOut.executeUpdate(ps_stat_uap);
		}
		rs.close();
	}
	

	public static void filtering(DbConn conn) throws Exception{
		//Create table
		//String query = "create table uapd (id bigint auto_increment primary key, u_id bigint, a_id bigint, p_id int, "
		//		+ "d int, index(u_id), index(a_id) )";
		//conn.executeUpdate(conn.getPreparedStatement(query));
		
		//Before screening:
		//	Users have at least 5 apps
		
		//Screening:
		//Users:
		//	#apps(who requested permissions) >= 10
		//	at least one non-default choice
		//Apps
		//	#users > 10
		//	exist in Google Play
		//  not white-listed (manual)
		//Decisions:
		//	remove all "default decisions"
		
		String query = "create table tmp_u "
				+ "select u_id, acount from "
				+ "(select u_id, count(distinct a_id) acount, max(d_final) dmax from stat_uap group by u_id) ua "
				+ "where acount >= 10 and dmax > 1";
		conn.executeUpdate(conn.getPreparedStatement(query)); //Slow
		
		query = "create table tmp_a select s.a_id, s.ucount, g.Id g_id from "
				+ "(select a_id, count(distinct u_id) ucount from stat_uap group by a_id) s "
				+ "join a on s.a_id = a.a_id "
				+ "join lbe_gplay.gplay g on a.a_name = g.packagename "
				+ "where s.ucount >= 10";
		conn.executeUpdate(conn.getPreparedStatement(query));
		
		//Remove white-listed apps from tmp_a (manual)
	
	}
}
